﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DX.CRC.Common.Entities;
using StuMngVan.Entities;
using System.Data;

namespace StuMngVan.Dals
{
    class Dal4Score : DalBase
    {
        public static List<Score> SearchAllScores(string name)
        {
            QueryObject qo = CreateQO();

            qo.CmdText = @"SELECT Sc.[ID]
	              ,St.Name AS Student
                  ,Cr.Name AS Course
                  ,Sc.[Score] as [Scores]
                  ,Sc.[Remark]
            FROM [dbo].[TbScore] Sc LEFT JOIN [dbo].[TbStudent] St on Sc.StudentID = St.ID
                LEFT JOIN [dbo].[TbCourse] Cr on Sc.CourseID=Cr.ID 
            WHERE Sc.IsActive = 1
                and St.Name like '%' + @Name + '%'";

            qo.AddParam("@Name", name);
            return qo.xExecuteList<Score>();
        }

        /// <summary>
        /// 【闻祖东 2013-4-2-230724】这个InsertScore方法，请用面向对象的方式封装其参数。
        /// </summary>
        /// <param name="studentID"></param>
        /// <param name="courseID"></param>
        /// <param name="score"></param>
        /// <param name="remark"></param>
        /// <returns></returns>
        public static bool InsertScore(int studentID, int courseID, int score, string remark)
        {
            QueryObject qo = CreateQO();

            qo.CmdText = @"declare @Count int;
                  select @Count=count(*) from TbScore where @StudentID=StudentID and @CourseID=CourseID;
                  if @Count=0
                  begin
                  INSERT INTO [dbo].[TbScore]
                                   ([StudentID]
                                   ,[CourseID]
                                   ,[Score]
                                   ,[IsActive]
                                   ,[CreateTime]
                                   ,[Remark])
                             VALUES
                                   (@StudentID
                                   ,@CourseID
                                   ,@Score
                                   ,1
                                   ,getutcdate()
                                   ,@Remark)
                   end;";

            qo.AddParam("@StudentID", studentID);
            qo.AddParam("@CourseID", courseID);
            qo.AddParam("@Score", score);
            qo.AddParam("@Remark", remark);

            return qo.xExecuteNonQuery() == 1;
        }

        public static void DeleteScore(Score score)
        {
            QueryObject qo = CreateQO();

            qo.CmdText = @"update TbScore 
                  set IsActive = 0
                  WHERE ID = @ID";

            qo.AddParam("@ID", score.ID);

            qo.xExecuteNonQuery();
        }

        public static DataTable GetCourses()
        {
            QueryObject qo = CreateQO();
            qo.CmdText = @"select ID as CourseID
	            , Name as CourseName
            from TbCourse";

            return qo.xExecuteDataTable();
        }

        /// <summary>
        /// 【闻祖东 2013-4-2-220815】你的这个类Dal4Score严格意义上的划分应该是只处理Score的业务，
        /// 而GetStudents则更应该放到Dal4Student里面去，不是吗？
        /// </summary>
        /// <returns></returns>
        public static DataTable GetStudents()
        {
            QueryObject qo = CreateQO();
            qo.CmdText = @"select ID as StudentID
	            , Name as StudentName
            from TbStudent";

            return qo.xExecuteDataTable();
        }

        public static List<Score> SearchAllScoreRanking(string courseName)
        {
            QueryObject qo = CreateQO();

            //            qo.CmdText = @"SELECT Sc.[ID]
            //	              ,St.Name AS Student
            //                  ,Cr.Name AS Course
            //                  ,Sc.Score as Scores
            //                  ,Sc.Remark
            //            FROM [dbo].[TbScore] Sc LEFT JOIN [dbo].[TbStudent] St on Sc.StudentID=St.ID
            //            LEFT JOIN [dbo].[TbCourse] Cr on Sc.CourseID=Cr.ID WHERE Sc.IsActive=1
            //            and Cr.Name=@Course
            //            order by Sc.Score desc";

            ///【闻祖东 2013-4-2-221002】其实这里SQL语句完全没有改过，修改的只是换行以及缩进，这样的代码才是让同事更好
            ///理解和阅读的代码。
            qo.CmdText = @"SELECT Sc.[ID]
	              ,St.Name AS Student
                  ,Cr.Name AS Course
                  ,Sc.Score as Scores
                  ,Sc.Remark
            FROM [dbo].[TbScore] Sc LEFT JOIN [dbo].[TbStudent] St on Sc.StudentID = St.ID
                LEFT JOIN [dbo].[TbCourse] Cr on Sc.CourseID = Cr.ID 
            WHERE Sc.IsActive = 1
                and Cr.Name = @Course
            order by Sc.Score desc";

            qo.AddParam("@Course", courseName);
            return qo.xExecuteList<Score>();
        }
    }
}
